package com.oasystem.dao.jdbc.fund;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.oasystem.dao.jdbc.BaseDAO;
import com.oasystem.domain.PagingBean;
import com.oasystem.domain.fund.FundApplication;
import com.oasystem.domain.fund.FundClassification;
import com.oasystem.domain.user.User;
import com.oasystem.enums.EApproveStatus;
import com.oasystem.utils.DateUtils;
import com.oasystem.utils.PrimaryKeyGenerator;
import com.oasystem.web.webbean.fund.FunOutcomeSummary;

public class FundApplicationDao extends BaseDAO {

	public List<FundApplication> findAll(Integer approverId, int start,
			int limit) {
		String sql = "SELECT * FROM FundApplication WHERE approverid=? ORDER BY id DESC LIMIT ?,?";
		return getJdbcTemplate().queryForList(sql,
				new Object[] { approverId, start, limit },
				FundApplication.class);
	}

	public void insert(FundApplication fundApplication) {
		String sql = "INSERT INTO fundApplication (id,classification,amount,consumeTime,applicantId,approver,approveStatus,applyTime) VALUES (?,?,?,?,?,?,?,?)";
		getJdbcTemplate().update(
				sql,
				new Object[] { PrimaryKeyGenerator.getUUID(),
						fundApplication.getClassification().getId(),
						fundApplication.getAmount(),
						fundApplication.getConsumeTime(),
						fundApplication.getApplicantId(),
						fundApplication.getApprover().getId(),
						fundApplication.getApproveStatus().getCode(),
						fundApplication.getApplyTime() });
	}

	public void update(FundApplication fundApplication) {
		String sql = "UPDATE fundApplication SET approveStatus=? WHERE id=?";
		getJdbcTemplate().update(
				sql,
				new Object[] { fundApplication.getApproveStatus().getCode(),
						fundApplication.getId() });
	}

	public void fundApplicationHistory(String applicant,
			PagingBean<FundApplication> pagingBean) {
		String sql = "SELECT * FROM fundapplication f,user u,fundclassification c WHERE f.approver=u.id and f.classification=c.id and applicantid=? order by applyTime desc LIMIT ?,?";
		pagingBean.setData(getJdbcTemplate().query(
				sql,
				new Object[] { applicant, pagingBean.getCurrentRecordIndex(),
						pagingBean.getPageSize() },
				new RowMapper<FundApplication>() {

					@Override
					public FundApplication mapRow(ResultSet rs, int index)
							throws SQLException {
						FundApplication fundApplication = new FundApplication();
						fundApplication.setId(rs.getString("f.id"));
						FundClassification classification = new FundClassification();
						classification.setName(rs.getString("c.name"));
						fundApplication.setClassification(classification);
						fundApplication.setAmount(rs.getDouble("f.amount"));
						fundApplication.setConsumeTime(DateUtils.format(
								"yyyy/MM/dd", rs.getTimestamp("f.consumeTime")));
						User approver = new User();
						approver.setName(rs.getString("u.name"));
						fundApplication.setApprover(approver);
						fundApplication.setApproveStatus(EApproveStatus
								.getByStatus(rs.getString("f.approveStatus")));
						fundApplication.setApplyTime(DateUtils.format(
								"yyyy/MM/dd HH:mm:ss",
								rs.getTimestamp("f.applyTime")));
						fundApplication.setRejectReason(rs
								.getString("f.rejectReason"));
						fundApplication.setApproveDate(DateUtils.format(
								"yyyy/MM/dd HH:mm:ss",
								rs.getTimestamp("f.approveTime")));
						return fundApplication;
					}

				}));
		sql = "SELECT COUNT(*) FROM fundapplication f,user u,fundclassification c WHERE f.approver=u.id and f.classification=c.id and applicantid=?";
		pagingBean.setAllRecordCount(getJdbcTemplate().queryForInt(sql,
				new Object[] { applicant }));
	}

	public void fundApplicationListForCheck(String approver,
			PagingBean<FundApplication> pagingBean) {
		String sql = "SELECT * FROM fundapplication f,user u,fundclassification c WHERE f.applicantId=u.id and f.classification=c.id and approver=? order by applyTime desc LIMIT ?,?";
		pagingBean.setData(getJdbcTemplate().query(
				sql,
				new Object[] { approver, pagingBean.getCurrentRecordIndex(),
						pagingBean.getPageSize() },
				new RowMapper<FundApplication>() {

					@Override
					public FundApplication mapRow(ResultSet rs, int index)
							throws SQLException {
						FundApplication fundApplication = new FundApplication();
						fundApplication.setId(rs.getString("f.id"));
						FundClassification classification = new FundClassification();
						classification.setName(rs.getString("c.name"));
						fundApplication.setClassification(classification);
						fundApplication.setAmount(rs.getDouble("f.amount"));
						fundApplication.setConsumeTime(DateUtils.format(
								"yyyy/MM/dd", rs.getTimestamp("f.consumeTime")));
						fundApplication.setApplicantName(rs.getString("u.name"));
						fundApplication.setApproveStatus(EApproveStatus
								.getByStatus(rs.getString("f.approveStatus")));
						fundApplication.setApplyTime(DateUtils.format(
								"yyyy/MM/dd HH:mm:ss",
								rs.getTimestamp("f.applyTime")));
						fundApplication.setRejectReason(rs
								.getString("f.rejectReason"));
						fundApplication.setApproveDate(rs
								.getString("f.approveTime"));
						return fundApplication;
					}

				}));
		sql = "SELECT COUNT(*) FROM fundapplication f,user u,fundclassification c WHERE f.approver=u.id and f.classification=c.id and approver=?";
		pagingBean.setAllRecordCount(getJdbcTemplate().queryForInt(sql,
				new Object[] { approver }));
	}

	public void approveFundApplication(String fundApplicationId) {
		String sql = "UPDATE fundapplication set approveStatus=?,approveTime=? where id=?";
		getJdbcTemplate().update(
				sql,
				new Object[] { EApproveStatus.APPROVED.getCode(),
						DateUtils.getCurrentTimeString(), fundApplicationId });

	}

	public void rejectFundApplication(String fundApplicationId, String reason) {
		String sql = "UPDATE fundapplication set approveStatus=?,approveTime=?,rejectReason=? where id=?";
		getJdbcTemplate().update(
				sql,
				new Object[] { EApproveStatus.DENY.getCode(),
						DateUtils.getCurrentTimeString(), reason,
						fundApplicationId });

	}

	public double totalFundOutcome() {
		String sql = "select sum(amount) from fundapplication where approveStatus='A'";
		return getJdbcTemplate().queryForObject(sql, Double.class);

	}

	public List<FunOutcomeSummary> fundOutcomeSummaryList() {
		String sql = "select c.name,sum(amount) as totalAmount from fundapplication f, fundclassification c  where f.classification = c.id and f.approveStatus='A' group by f.classification";
		return getJdbcTemplate().query(sql, new RowMapper<FunOutcomeSummary>() {

			@Override
			public FunOutcomeSummary mapRow(ResultSet rs, int index)
					throws SQLException {
				FunOutcomeSummary summary = new FunOutcomeSummary();
				summary.setCatagoryName(rs.getString("c.name"));
				summary.setTotalAmount(rs.getDouble("totalAmount"));
				return summary;
			}
		});
	}
	
	public List<FunOutcomeSummary> fundOutcomeSummaryList(String from , String to) {
		String sql = "select c.name,sum(amount) as totalAmount from fundapplication f, fundclassification c  where f.classification = c.id and f.approveStatus='A' and f.approveTime between ? and ? group by f.classification";
		return getJdbcTemplate().query(sql,new Object[]{from,to}, new RowMapper<FunOutcomeSummary>() {

			@Override
			public FunOutcomeSummary mapRow(ResultSet rs, int index)
					throws SQLException {
				FunOutcomeSummary summary = new FunOutcomeSummary();
				summary.setCatagoryName(rs.getString("c.name"));
				summary.setTotalAmount(rs.getDouble("totalAmount"));
				return summary;
			}
		});
	}
}